﻿using System;
using System.Collections.Generic;
using System.Text;
using NUnit.Framework;
using Oragon.Architecture.Data.SqlBuilder;
using Oragon.Architecture.Data.SqlBuilder.Operators;
using Oragon.Architecture.Data.QueryBuilder.SqlServer;

namespace Oragon.Architecture.Data.QueryBuilder.Tests
{
    [TestFixture(Description = "Teste para Oragon.Architecture.Data.SqlBuilder.SelectSQLBuilder")]
    public class SelectSQLBuilderTest
    {


        [Test()]
        public void SelectSQLBuilderTest_SelectSimples_com_parametro_opicional()
        {
            SelectSQLBuilder target = new SelectSQLBuilder(); // TODO: Initialize to an appropriate value
			target.Tables.Add(new FromItem(LiteralManager.GetLiteralBuilder().BuildTableName("dataBaseName", "dbo", "tableOfView"), "con"));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("idOfTable")));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("Site"), "Nome"));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName01")));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName02")));
            //target.WhereClause.Items.Add( new AndOperator( new IsNotNullOperator(new SQLLiteral("columnName02"))));

            OrBinaryOperator orBinaryOperator = new OrBinaryOperator(
															new IsNullOperator(LiteralManager.GetLiteralBuilder().BuildParameter("columnName02")),
                                                            new EqualToOperator(
																				LiteralManager.GetLiteralBuilder().BuildColumnName("columnName02"),
																				LiteralManager.GetLiteralBuilder().BuildParameter("columnName02")
                                                                                )
                                                            );
            target.WhereClause.Items.Add(orBinaryOperator);
            string actual = target.ToSQL();
            Assert.AreEqual(Oragon.Architecture.Data.QueryBuilder.Tests.Properties.Resources.SelectSQLBuilderTest_SelectSimples_com_parametro_opicional, actual.Trim());
        }


        [Test()]
        public void SelectSQLBuilderTest_BetweenOperator()
        {
            SelectSQLBuilder target = new SelectSQLBuilder(); // TODO: Initialize to an appropriate value
			target.Tables.Add(new FromItem(LiteralManager.GetLiteralBuilder().BuildTableName("dataBaseName", "dbo", "tableOfView"), "con"));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("idOfTable")));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("Site"), "Nome"));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName01")));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName02")));
			target.WhereClause.Items.Add(new AndUnaryOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));

            string actual = target.ToSQL();
            Assert.AreEqual(Oragon.Architecture.Data.QueryBuilder.Tests.Properties.Resources.SelectSQLBuilderTest_BetweenOperator, actual.Trim());
        }

		[Test()]
		public void SelectSQLBuilderTest_AndOrNotLike()
		{
			SelectSQLBuilder target = new SelectSQLBuilder(); // TODO: Initialize to an appropriate value
			target.Tables.Add(new FromItem(LiteralManager.GetLiteralBuilder().BuildTableName("dataBaseName.dbo.tableOfView"), "con"));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("idOfTable")));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("Site"), "Nome"));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName01")));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName02")));
			target.WhereClause.Items.Add(
				new AndUnaryOperator(
					new BetweenOperator(
						LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"),
						LiteralManager.GetLiteralBuilder().BuildInt(1), 
						LiteralManager.GetLiteralBuilder().BuildInt(10)
					)
				)
			);
			target.WhereClause.Items.Add(
				new OrUnaryOperator(
					new BetweenOperator(
						LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"),
						LiteralManager.GetLiteralBuilder().BuildInt(1),
						LiteralManager.GetLiteralBuilder().BuildInt(10)
					)
				)
			);
			target.WhereClause.Items.Add(
				new NotOperator(
					new BetweenOperator(
						LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"),
						LiteralManager.GetLiteralBuilder().BuildInt(1),
						LiteralManager.GetLiteralBuilder().BuildInt(10)
					)
				)
			);
			target.WhereClause.Items.Add(
				new LikeToOperator(
					LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"),
					LiteralManager.GetLiteralBuilder().BuildParameter("param")
				)
			);

			string expected = @"SELECT 
	[idOfTable]
	,[Site] as Nome
	,[columnName01]
	,[columnName02]
FROM 
	[dataBaseName].[dbo].[tableOfView] as con
WHERE 
	( [CampoIntA] BETWEEN 1 AND 10 )
	OR ( ( [CampoIntA] BETWEEN 1 AND 10 ) )
	AND ( NOT ( ( [CampoIntA] BETWEEN 1 AND 10 ) ) )
	AND ( ( [CampoIntA] LIKE @param ) )
";

			string actual;
			actual = target.ToSQL();
			Assert.AreEqual(expected, actual);
		}

        [Test()]
        public void SelectSQLBuilderTest_ComOrderBy()
        {
            SelectSQLBuilder target = new SelectSQLBuilder(); // TODO: Initialize to an appropriate value
			target.Tables.Add(new FromItem(LiteralManager.GetLiteralBuilder().BuildTableName("dataBaseName", "dbo", "tableOfView"), "con"));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("idOfTable")));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("Site"), "Nome"));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName01")));
			target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName02")));
			target.WhereClause.Items.Add(new AndUnaryOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));
			target.WhereClause.Items.Add(new OrUnaryOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));
			target.WhereClause.Items.Add(new NotOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));
			target.WhereClause.Items.Add(new LikeToOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildParameter("param")));
            target.OrderBy.Add(LiteralManager.GetLiteralBuilder().BuildOrderBy("idOfTable", Sorting.Ascending));
            string actual = target.ToSQL().Trim();
            Assert.AreEqual(Oragon.Architecture.Data.QueryBuilder.Tests.Properties.Resources.SelectSQLBuilderTest_ComOrderBy, actual);
        }


        [Test()]
        public void SelectSQLBuilderTest_ClonaEComparaSQL()
        {
            SelectSQLBuilder target = new SelectSQLBuilder(); // TODO: Initialize to an appropriate value
            target.TopClause.Quantity = 2;
            target.TopClause.Unit = TopUnit.Percent;
			target.Tables.Add(new FromItem(LiteralManager.GetLiteralBuilder().BuildTableName("dataBaseName", "dbo", "tableOfView"), "con"));
            target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("idOfTable")));
            target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("Site]"), "Nome"));
            target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName01")));
            target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName02")));
            target.WhereClause.Items.Add(new AndUnaryOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));
            target.WhereClause.Items.Add(new OrUnaryOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));
            target.WhereClause.Items.Add(new NotOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));
            target.WhereClause.Items.Add(new LikeToOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildColumnName("@param")));
            target.OrderBy.Add(LiteralManager.GetLiteralBuilder().BuildOrderBy("idOfTable", Sorting.Ascending));
            SelectSQLBuilder target2 = target.Clone();
            Assert.AreEqual(target.ToSQL(), target2.ToSQL());
        }


        [Test()]
        public void SelectSQLBuilderTest_ClonaModificaEComparaSQL()
        {
            SelectSQLBuilder target = new SelectSQLBuilder(); // TODO: Initialize to an appropriate value
            target.TopClause.Quantity = 2;
            target.TopClause.Unit = TopUnit.Percent;
			target.Tables.Add(new FromItem(LiteralManager.GetLiteralBuilder().BuildTableName("dataBaseName", "dbo", "tableOfView"), "con"));
            target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("idOfTable")));
            target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("Site]"), "Nome"));
            target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName01")));
            target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName02")));
            target.WhereClause.Items.Add(new AndUnaryOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));
            target.WhereClause.Items.Add(new OrUnaryOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));
            target.WhereClause.Items.Add(new NotOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));
            target.WhereClause.Items.Add(new LikeToOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildParameter("param")));
            target.OrderBy.Add(LiteralManager.GetLiteralBuilder().BuildOrderBy("idOfTable", Sorting.Ascending));

            SelectSQLBuilder target2 = target.Clone();
            target.WhereClause.Items.Add(new LikeToOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntB"), LiteralManager.GetLiteralBuilder().BuildParameter("param")));

            Assert.AreNotEqual(target.ToSQL(), target2.ToSQL());
        }

        [Test()]
        public void SelectSQLBuilderTest_ClonaModificaAmbosEComparaSQL()
        {
            SelectSQLBuilder target = new SelectSQLBuilder(); // TODO: Initialize to an appropriate value
            target.TopClause.Quantity = 2;
            target.TopClause.Unit = TopUnit.Percent;
			target.Tables.Add(new FromItem(LiteralManager.GetLiteralBuilder().BuildTableName("dataBaseName", "dbo", "tableOfView"), "con"));
            target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("idOfTable")));
            target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("Site]"), "Nome"));
            target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName01")));
            target.Columns.Add(new ColumnItem(LiteralManager.GetLiteralBuilder().BuildColumnName("columnName02")));
            target.WhereClause.Items.Add(new AndUnaryOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));
            target.WhereClause.Items.Add(new OrUnaryOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));
            target.WhereClause.Items.Add(new NotOperator(new BetweenOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildInt(1), LiteralManager.GetLiteralBuilder().BuildInt(10))));
            target.WhereClause.Items.Add(new LikeToOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntA"), LiteralManager.GetLiteralBuilder().BuildColumnName("param")));
            target.OrderBy.Add(LiteralManager.GetLiteralBuilder().BuildOrderBy("idOfTable", Sorting.Ascending));

            SelectSQLBuilder target2 = target.Clone();
            target.WhereClause.Items.Add(new LikeToOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntB"), LiteralManager.GetLiteralBuilder().BuildParameter("param")));
            target2.WhereClause.Items.Add(new LikeToOperator(LiteralManager.GetLiteralBuilder().BuildColumnName("CampoIntB"), LiteralManager.GetLiteralBuilder().BuildParameter("param")));

            Assert.AreEqual(target.ToSQL(), target2.ToSQL());
        }


    }
}
